﻿CREATE PROCEDURE [maint].[ScheduledDbMaintenance]
@MaintenanceAction CHAR (1)=NULL, @CheckSchedule BIT=1
AS
BEGIN
	DECLARE @RC int;
	SET @RC = 0;

	DECLARE @OutputDiagnostics bit;
	SELECT @OutputDiagnostics = CAST([Value] AS bit)
	FROM [config].[InstanceSettings]
	WHERE ([Name] = N'MaintenanceDiagnostics');
	
	IF(@OutputDiagnostics = 1)
		PRINT OBJECT_NAME(@@PROCID);
			
	DECLARE @MaintenanceProcedure nvarchar(128);
	SET @MaintenanceProcedure = CASE @MaintenanceAction
		WHEN 'B' THEN 'BackupToDevice'
		WHEN 'I' THEN 'DefragDb'
		WHEN 'C' THEN 'CheckDB'
		WHEN 'S' THEN 'UpdateStats'
	END;
	
	IF (@MaintenanceProcedure IS NULL)
	BEGIN
		SET @RC = 1;
		RAISERROR('Invalid maintenance action specified (%s). Valid options are: B[Db Backup],I[Index Defrag],C[CHECKDB],S[Stats Update]',16,1,@MaintenanceAction);
		GOTO EXIT_PROC;
	END;	
	
	DECLARE DB_CUR CURSOR FOR(
		SELECT [name]		
		FROM [master].[sys].[databases]
		LEFT JOIN [config].[DbMaintenance]
			ON ([DbName] = [name] COLLATE Latin1_General_CI_AS)
		WHERE  (DATABASEPROPERTYEX([name],'Status') <> N'OFFLINE')
			AND(DATABASEPROPERTY([name],'IsInStandBy') = 0) --not log shipped
			AND(
					([DbName] IS NOT NULL) --db is configured
					OR([source_database_id] IS NULL) --snapshot db's excluded by default
				)
			AND([name] NOT IN ('model','tempdb'))
			AND(ISNULL([Enabled],1) = 1)
	) ORDER BY [name];
	OPEN DB_CUR;
	DECLARE @DbName sysname;
	FETCH NEXT FROM DB_CUR INTO @DbName;
	WHILE(@@FETCH_STATUS = 0)
	BEGIN
		BEGIN TRY			
			BEGIN
				DECLARE @PRC INT, @ExecStatement NVARCHAR(255);
				SET @PRC = 0;
				SET @ExecStatement = N'EXEC [maint].[' + @MaintenanceProcedure + '] @DbName, @CheckSchedule, @OutputDiagnostics;';

				EXEC @PRC = sp_executesql @ExecStatement
					,N'@DbName sysname, @CheckSchedule bit, @OutputDiagnostics bit'
					,@DbName, @CheckSchedule, @OutputDiagnostics; 
				
				IF(@PRC <> 0)
					RAISERROR('Maintenance for database %s failed with error number %i',16,1,@DbName, @PRC);
			END
		END TRY
		BEGIN CATCH
			PRINT REPLICATE('*',100);
			SET @RC = ERROR_NUMBER();
			PRINT 'Msg ' + CAST(ERROR_NUMBER() AS NVARCHAR) + N', Level ' + CAST(ERROR_SEVERITY() AS NVARCHAR) + N', State ' + CAST(ERROR_STATE() AS NVARCHAR) + N', Line ' + CAST(ERROR_LINE() AS NVARCHAR);
			PRINT ERROR_MESSAGE();
			PRINT REPLICATE('*',100);
		END CATCH
		FETCH NEXT FROM DB_CUR INTO @DbName;
	END;
	CLOSE DB_CUR;
	DEALLOCATE DB_CUR;

	IF(@RC <> 0)
		RAISERROR('Scheduled Database Maintenance encountered an error: %i',16,1,@RC);

EXIT_PROC:		
	RETURN(@RC); --last error returned
END